package sim.ring.technical.data;

import java.sql.*;
import java.util.Iterator;
import java.util.Vector;

import org.apache.log4j.*;

public class DatabaseConnection {
	private String driver = "org.apache.derby.jdbc.EmbeddedDriver";
	private Logger log = Logger.getLogger("sim.ring.technical.data");
	private Connection conn;
	
	
	public DatabaseConnection() {
		log.setLevel(Level.DEBUG);
	}
	
	public void connectToDB(String dbName) throws SQLException {
		try {
			Class.forName(driver);
		} catch (ClassNotFoundException e) {
			log.error("ClassNotFoundException: ");
			log.error(e.getMessage());
			log.error("Could not find Apache Derby class!");
			log.error("Check CLASSPATH variable is set correctly");
		}
		String connectionURL = "jdbc:derby:" + dbName + ";create=true";;
		conn = DriverManager.getConnection(connectionURL);
		log.info("Connected to database " + conn.getMetaData().getURL());
	}
	
	public void closeDB() {
		try {
			if (conn!=null) { 
				log.info("Closing connection to " + conn.getMetaData().getURL()+"..");
			} else { 
				return;
			}
			conn.rollback();
			conn.close();
		} catch (Exception e) {
			//NOT INTERESTED
		}
		log.info("..complete");
	}
	
	public ResultSet issueQueryStatement(String sql, Vector<Object> params) throws SQLException {
		if (params==null) {
			params=new Vector<Object>();
		}
		PreparedStatement ps = buildPreparedStatement(sql,params);
		writeLog(sql,params);
		ResultSet rs = ps.executeQuery();
		return rs;
	}
	
	public int issueNonQueryStatement(String sql, Vector<Object> params) throws SQLException {
		if (params==null) {
			params=new Vector<Object>();
		}
		PreparedStatement ps = buildPreparedStatement(sql,params);
		writeLog(sql,params);
		ps.executeUpdate();
		
		ResultSet rs=ps.getGeneratedKeys();
		int key = 0;
		if (rs!=null) {
			rs.next();
			key = rs.getInt(1);
		}
		try { rs.close(); } catch (Exception e) { /* ignored */ }
		try { ps.close(); } catch (Exception e) { /* ignored */ }
		return key;
	}
	
	public boolean checkForTable(String tableName) {
		boolean result = false;
		try {
			DatabaseMetaData dbMD = conn.getMetaData();
			ResultSet check = dbMD.getTables(null, null, tableName, null);
			if (check.next() && check.getString(3).equals(tableName)) {
				result = true;
			}
		} catch (SQLException e) {
			log.error(e.getMessage());
		}
		return result;
	}
	
	private PreparedStatement buildPreparedStatement(String sql, Vector<Object> params) {
		Iterator<Object> it = params.iterator();
		PreparedStatement ps=null;
		try {
			ps = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS);
			int i=1;
			while (it.hasNext()) {
				Object o = it.next();
				if (o instanceof String) {
					ps.setString(i,(String)o);
				} else if (o instanceof Integer) {
					ps.setInt(i,(Integer)o);
				} else if (o instanceof Float) {
					ps.setFloat(i, (Float)o);
				} else {
					log.error("Type unhandled");
					//TODO: raise type unhandled exception
				}
				i++;
			}
		} catch (SQLException e) {
			Iterator<Throwable> exc = e.iterator();
			while (exc.hasNext()) {
				log.error(exc.next().getMessage());
			}
		} 
		return ps;
	}
	
	private void writeLog(String sql, Vector<Object> params) {
		log.debug("Issuing statement:");
		log.debug("> "+sql);
		if (params.size() > 0) {
			log.debug("..with params:");
			Iterator<Object> it = params.iterator();
			while(it.hasNext()) {
				log.debug("> "+it.next());
			}
		}
	}
}
